BEGIN TRANSACTION
GO
ALTER TABLE dbo.Voucher ADD
	AVNo varchar(15) NULL,
	AVDate datetime NULL,
	TotalDays numeric(9, 2) NOT NULL CONSTRAINT DF_Voucher_TotalDays DEFAULT 0,
	AdjustDays numeric(9, 2) NOT NULL CONSTRAINT DF_Voucher_AdjustDays DEFAULT 0,
	NetDays numeric(9, 2) NOT NULL CONSTRAINT DF_Voucher_NetDays DEFAULT 0
GO
COMMIT

-----------------------------------------------------------

BEGIN TRANSACTION
GO
UPDATE VoucherDetails SET Amount=0 WHERE VoucherID IN (SELECT VoucherID FROM Voucher WHERE VoucherNo Like 'AI-%')
GO
COMMIT

-----------------------------------------------------------

USE [BSC]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 12/08/2019 16:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.CustomerName, 
                      dbo.Accounts.Remarks AS CustomerAddress, dbo.Accounts.CellNo, dbo.Accounts.Phone, dbo.Accounts.NameUrdu, dbo.Accounts.AreaID, dbo.Voucher.ReadOnly, 
                      dbo.Voucher.Amount AS SumAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.Voucher.TotalAmount, dbo.Voucher.NetAmount, dbo.Voucher.Cash, 
                      dbo.Voucher.GroupID AS SubPartyID, ISNULL(dbo.Groups.GroupName, '.') AS SubPartyName, dbo.Voucher.AVNo,
dbo.Voucher.AVDate, dbo.Voucher.TotalDays, dbo.Voucher.AdjustDays, dbo.Voucher.NetDays,dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, 
                      dbo.Accounts.AccountTitle, dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, dbo.Voucher.StoreID, dbo.Voucher.VehicleNo, dbo.Voucher.LoginID, 
                      dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, Accounts_2.AccountTitle AS BankAccountTitle, 
                      dbo.VoucherDetails.Amount AS ActualAmount, dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, 
                      dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.VoucherDetails.Qty, 
                      dbo.VoucherDetails.Rate, dbo.vw_Items.GroupID, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.VoucherDetails.RackID, dbo.Rack.RackCode, 
                      dbo.Rack.RackName, dbo.Rack.RackNameUrdu, dbo.VoucherDetails.ManzalID, dbo.Manzal.ManzalCode, dbo.Manzal.ManzalName, dbo.Manzal.ManzalNameUrdu, 
                      dbo.VoucherDetails.RoomID, dbo.Room.RoomCode, dbo.Room.RoomName, dbo.Room.RoomNameUrdu, dbo.VoucherDetails.LotNo, dbo.VoucherDetails.Marka
FROM         dbo.Manzal RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.Manzal.ManzalID = dbo.VoucherDetails.ManzalID LEFT OUTER JOIN
                      dbo.Rack ON dbo.VoucherDetails.RackID = dbo.Rack.RackID LEFT OUTER JOIN
                      dbo.Room ON dbo.VoucherDetails.RoomID = dbo.Room.RoomID LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Groups RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Groups.GroupID = dbo.Voucher.GroupID ON Accounts_2.AccountNo = dbo.Voucher.BankAccountID ON 
                      Employees_1.EmployeeID = dbo.Voucher.ModifyID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID

-------------------------------------------